﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.IO

Public Class MyObj
    Public Shared Function GetCodeMappingData(ByVal tableName As String, ByVal columnName As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select cm.* ")
        sql.AppendFormat("from FD_CodeMapping cm ")
        sql.AppendFormat("where cm.TableName = '{0}' ", tableName)
        sql.AppendFormat("and cm.ColumnName = '{0}' ", columnName)
        sql.AppendFormat("and cm.Active = '{0}' ", "Y")
        sql.AppendFormat("order by cm.Sequence ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    Public Shared Function GetCodeMappingData(ByVal tableName As String, ByVal columnName As String, ByVal columnExpression As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select cm.*, {0} ", columnExpression)
        sql.AppendFormat("from FD_CodeMapping cm ")
        sql.AppendFormat("where cm.TableName = '{0}' ", tableName)
        sql.AppendFormat("and cm.ColumnName = '{0}' ", columnName)
        sql.AppendFormat("and cm.Active = '{0}' ", "Y")
        sql.AppendFormat("order by cm.Sequence ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 取得鄉鎮資料
    ''' </summary>
    ''' <param name="countyID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetTownData(ByVal countyID As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select r.* ")
        sql.AppendFormat("from Region r ")
        sql.AppendFormat("where r.Parent = '{0}' ", countyID)
        sql.AppendFormat("order by r.Sequence ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 取得村里資料
    ''' </summary>
    ''' <param name="townID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetVillageData(ByVal townID As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select r.* ")
        sql.AppendFormat("from Region r ")
        sql.AppendFormat("where r.Parent = '{0}' ", townID)
        sql.AppendFormat("order by r.RegionID ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 取得道路資料
    ''' </summary>
    ''' <param name="townID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetRoadData(ByVal townID As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select r.* ")
        sql.AppendFormat("from FD_Road r ")
        sql.AppendFormat("where r.Parent = '{0}' ", townID)
        sql.AppendFormat("order by r.RoadID ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 取得細項道路資料
    ''' </summary>
    ''' <param name="RoadID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetRoadItemData(ByVal RoadID As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select r.* ")
        sql.AppendFormat("from FD_RoadItem r ")
        sql.AppendFormat("where r.RoadID = '{0}' ", RoadID)
        sql.AppendFormat("order by r.RoadItemName ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 取得鄉鎮道路資料
    ''' </summary>
    ''' <param name="townID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetRegionRoadData(ByVal townID As String) As DataTable
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select r.* ")
        sql.AppendFormat("from RegionRoad r ")
        sql.AppendFormat("where r.Parent = '{0}' ", townID)
        sql.AppendFormat("order by r.RoadID ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    ''' <summary>
    ''' 依登入者權限列出所有組織
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDDLOrganizationData() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select ua.*, o.OrganizationName, UOrganizationID ")
        sql.AppendFormat("from FD_UserOrganizationAuth ua ")
        sql.AppendFormat("inner join FD_OrganizationView o on ua.OrganizationID = o.OrganizationID and ua.VOrganizationID = o.VOrganizationID ")
        sql.AppendFormat("where ua.UserID = '{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and o.VOrganizationID = -1 ")
        sql.AppendFormat("order by o.VOrganizationCode, o.OrganizationCode ")

        dt = dat.GetData(sql)

        Return dat.GetData(sql)
    End Function

    Public Shared Function GetDDLVOrganizationData() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select ua.*, o.VOrganizationName, o.OrganizationName, UOrganizationID ")
        sql.AppendFormat("from FD_UserOrganizationAuth ua ")
        sql.AppendFormat("inner join VOrganizationView o on ua.OrganizationID = o.OrganizationID and ua.VOrganizationID = o.VOrganizationID ")
        sql.AppendFormat("where ua.UserID = '{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and o.OrganizationID = -1 ")
        'sql.AppendFormat("order by o.VOrganizationCode, o.OrganizationCode ")

        dt = dat.GetData(sql)

        Return dat.GetData(sql)
    End Function

    Public Shared Function GetDDLInsertParentProgram() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder

        sql.AppendFormat("select pg.ProgramGroupID, p.ProgramName from FD_ProgramGroup pg ")
        sql.AppendFormat("inner join FD_Program p on pg.ProgramGroupID = p.ProgramID ")
        sql.AppendFormat("where pg.UserEditable='Y' ")
        sql.AppendFormat("order by p.Parent, p.Sequence ")

        Return dat.GetData(sql)

        'Me.ddlProgramGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Function

    Public Shared Function GetDDLProgramVisiable() As DataTable
        Return MyObj.GetCodeMappingData("Program", "Visible")

    End Function

    Public Shared Function GetDDLProgramTarget() As DataTable
        Return MyObj.GetCodeMappingData("Program", "Target")
    End Function

    Public Shared Function GetDDLParentProgram() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder

        sql.AppendFormat("select p.ProgramID, p.ProgramName ")
        sql.AppendFormat("from ( ")
        sql.AppendFormat("select ProgramGroupID = ProgramID from FD_Program p ")
        sql.AppendFormat("where p.Parent=0 or p.ProgramID = 0 ")
        sql.AppendFormat("union select pg.ProgramGroupID ")
        sql.AppendFormat("from FD_ProgramGroup pg ) pg ")
        sql.AppendFormat("inner join FD_Program p on pg.ProgramGroupID = p.ProgramID ")
        sql.AppendFormat("order by p.Parent, p.Sequence ")

        Return dat.GetData(sql)

        'Me.ddlProgramGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Function

    Public Shared Function GetMaxSequence(ByVal Parent As String) As String
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As New DataTable

        sql.AppendFormat("select isnull(max(Sequence)+1,'0') from FD_program ")
        sql.AppendFormat("where parent='{0}' ", Parent)

        dt = dat.GetData(sql)
        Return dt.Rows(0).Item(0).ToString()

        'Me.ddlProgramGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Function

    Public Shared Function GetMaxProgramID(ByVal Parent As String) As String
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As New DataTable

        sql.AppendFormat("select dbo.FD_GetMaxProgram('{0}') ", Parent)

        dt = dat.GetData(sql)
        Return dt.Rows(0).Item(0).ToString()

        'Me.ddlProgramGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Function

    ''' <summary>
    ''' 依登入者權限列出所有運用單位
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDDLDirectOrganizationData() As DataTable
        Dim dat As New clsData
        Dim sql As New StringBuilder
        Dim dt As Data.DataTable

        sql.Length = 0
        sql.AppendFormat("select ua.*, o.OrganizationName, UOrganizationID ")
        sql.AppendFormat("from FD_UserOrganizationAuth ua ")
        sql.AppendFormat("inner join FD_OrganizationView o on ua.OrganizationID = o.OrganizationID and ua.VOrganizationID = o.VOrganizationID ")
        sql.AppendFormat("where ua.UserID = '{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and o.OrganizationType in ('O','P') ")
        sql.AppendFormat("order by o.VOrganizationCode, o.OrganizationCode ")

        dt = dat.GetData(sql)

        Return dat.GetData(sql)
    End Function

    ''' <summary>
    ''' 取得參數
    ''' </summary>
    ''' <param name="parameterType"></param>
    ''' <param name="parameterName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetParameter(ByVal parameterType As String, ByVal parameterName As String) As String
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select ParameterValue ")
        sql.AppendFormat("from FD_SystemParameter p ")
        sql.AppendFormat("where ParameterType = '{0}' ", parameterType)
        sql.AppendFormat("and ParameterName = '{0}' ", parameterName)

        Return dat.GetData(sql).Rows(0)(0).ToString()
    End Function

    ''' <summary>
    ''' 取得髒亂點通報流水號
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetCasePassword() As String
        Dim sql As New StringBuilder
        Dim dat As New clsData
        Dim dt As DataTable
        Dim newCasePassword As String
        Dim ParameterText As String = Right("0" & Date.Today.Year - 1911, 3) & Right("0" & Date.Today.Month, 2)
        Dim ParameterType As String = "Count"
        Dim ParameterName As String = "CasePassword"

        sql.AppendFormat("select ParameterValue+1 as ParameterValue,ParameterText ")
        sql.AppendFormat("from FD_SystemParameter p ")
        sql.AppendFormat("where ParameterType = '{0}' ", ParameterType)
        sql.AppendFormat("and ParameterName = '{0}' ", ParameterName)
        sql.AppendFormat("and ParameterText = '{0}' ", ParameterText)

        dt = dat.GetData(sql)
        sql.Length = 0
        If dt.Rows.Count > 0 Then
            newCasePassword = ParameterText & Right("0000" & dt.Rows(0)("ParameterValue").ToString(), 4)
            sql.AppendFormat("Update FD_SystemParameter ")
            sql.AppendFormat("set ParameterValue=ParameterValue+1 ")
            sql.AppendFormat("where ParameterType = '{0}' ", ParameterType)
            sql.AppendFormat("and ParameterName = '{0}' ", ParameterName)
            sql.AppendFormat("and ParameterText = '{0}' ", ParameterText)
        Else
            newCasePassword = ParameterText & "0001"
            sql.AppendFormat("Insert into FD_SystemParameter (ParameterType,ParameterName,ParameterText,ParameterValue,Active) select")
            sql.AppendFormat(" '{0}' ", ParameterType)
            sql.AppendFormat(" ,'{0}' ", ParameterName)
            sql.AppendFormat(" ,'{0}' ", ParameterText)
            sql.AppendFormat(" ,'{0}' ", "1")
            sql.AppendFormat(" ,'Y'")
        End If
        dat.AccData(sql.ToString())

        Return newCasePassword
    End Function

    Public Shared Function GetDDLSelectAllItem() As ListItem
        Dim li As New ListItem

        li.Text = "----全部----"
        li.Value = ""

        Return li
    End Function

    Public Shared Function GetDDLPleaseChooseItem() As ListItem
        Dim li As New ListItem

        li.Text = "----請選擇----"
        li.Value = ""

        Return li
    End Function

    Public Shared Sub ShowMessage(ByVal message As String)
        Dim js As String

        js = String.Format("alert('{0}');", message)
        ScriptManager.RegisterClientScriptBlock(TryCast(HttpContext.Current.Handler, Page), GetType(String), message.ToString, js, True)
    End Sub

    Public Shared Sub ShowMessage(ByVal format As String, ByVal ParamArray args() As Object)
        MyObj.ShowMessage(String.Format(format, args))
    End Sub

    Public Shared Sub WriteLog(ByVal message As String)
        Dim fileName As String = HttpContext.Current.Server.MapPath(String.Format("~\log\{0:yyyyMMdd}.txt", DateTime.Now))
        Dim sr As New IO.StreamWriter(fileName, True)

        sr.WriteLine(message)

        sr.Close()
    End Sub

    ''' <summary>
    ''' 取得新增完畢之 row id
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Function GetIdentity() As Integer
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select @@identity ")

        Return dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt").Tables(0).Rows(0)(0)
    End Function

    ''' <summary>
    ''' 匯出 Excel時使用
    ''' </summary>
    ''' <param name="gv"></param>
    ''' <param name="dataField"></param>
    ''' <param name="headerText"></param>
    ''' <remarks></remarks>
    Public Shared Sub AddBoundField(ByVal gv As GridView, ByVal dataField As String, ByVal headerText As String)
        Dim field As New BoundField
        field.DataField = dataField
        field.HeaderText = headerText
        gv.Columns.Add(field)
    End Sub

    Public Shared Sub ExportToExcel(ByVal gv As GridView, ByVal fileName As String)
        Dim tw As System.IO.StringWriter = New System.IO.StringWriter
        Dim hw As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw)
        Dim response As HttpResponse = HttpContext.Current.Response

        gv.AutoGenerateColumns = False

        'hw.WriteLine("<form runat=server>");        	

        response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName)
        response.ContentType = "application/vnd.ms-excel"
        response.Charset = "utf-8"
        gv.Page.ResponseEncoding = "utf-8"

        gv.RenderControl(hw)

        '//this.EnableViewState = false;
        response.Write(tw.ToString())
        response.End()
    End Sub

    ''' <summary>
    ''' 取得登入者所屬單位下的社區發展協會志工隊
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetPOrganization() As DataTable
        Dim sql As New MyOrganizationDataFilter
        Dim dat As New clsData

        sql.AppendFormat("select * ")
        sql.AppendFormat("from GetPOrganizationTree('{0}') ", clsAuth.GetUser().UOrganizationID)

        Return dat.GetData(sql)
    End Function

    '傳回道路總稱
    Public Shared Function GetRoadName(ByVal RoadID As String) As String
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select RoadName ")
        sql.AppendFormat("from FD_Road ")
        sql.AppendFormat("where RoadID = '{0}' ", RoadID)
        Dim Dt As DataTable = dat.GetData(sql)
        If Dt.Rows.Count > 0 Then
            Return dat.GetData(sql).Rows(0)(0).ToString()
        Else
            Return ""
        End If

    End Function

    '傳回是否有此案件編號資料
    Public Shared Function HasInsert(ByVal CasePassword As String) As Boolean
        Dim sql As New StringBuilder
        Dim dat As New clsData

        sql.AppendFormat(" select CasePassword ")
        sql.AppendFormat(" from FD_FindDirtyHeader ")
        sql.AppendFormat(" where CasePassword = '{0}' ", CasePassword)
        Dim Dt As DataTable = dat.GetData(sql)
        If Dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Sub MatchItem(ByVal Item As DropDownList, ByVal Value As String)
        Item.SelectedIndex = Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))
    End Sub

    Public Shared Sub MatchItem(ByVal Item As RadioButtonList, ByVal Value As String)
        Item.SelectedIndex = Item.Items.IndexOf(Item.Items.FindByValue(Value.Trim()))
    End Sub

    Public Shared Function ReadFile(ByVal filePath As String) As Byte()
        Dim fs As FileStream

        Try
            ' Read file and return contents
            fs = File.Open(filePath, FileMode.Open, FileAccess.Read)

            Dim lngLen As Long = fs.Length
            Dim abytBuffer(CInt(lngLen - 1)) As Byte
            fs.Read(abytBuffer, 0, CInt(lngLen))
            Return abytBuffer

        Finally
            ' Make sure that file stream is 
            ' closed even if an error occurs.
            fs.Close()
        End Try
    End Function

    Public Shared Function GetCreateBySQL() As MyStringBuilder
        Dim sql As New MyStringBuilder

        sql.AppendFormat(", getdate() ")
        sql.AppendFormat(", @CreateBy ")

        sql.AddWithValue("CreateBy", HttpContext.Current.User.Identity.Name)

        Return sql
    End Function

    Public Shared Function GetUpdateBySQL() As MyStringBuilder
        Dim sql As New MyStringBuilder

        sql.AppendFormat(", UpdateDate = getdate() ")
        sql.AppendFormat(", UpdateBy = @UpdateBy ")

        sql.AddWithValue("UpdateBy", HttpContext.Current.User.Identity.Name)

        Return sql
    End Function

    '傳回國字星期
    Public Shared Function GetWeekNum(ByVal strData As String) As String
        Dim strDayOfWeek As String = Convert.ToDateTime(strData).DayOfWeek.ToString()
        Dim intWeekday As String = ""
        Select Case strDayOfWeek
            Case "Monday"
                intWeekday = "一"
                Exit Select
            Case "Tuesday"
                intWeekday = "二"
                Exit Select
            Case "Wednesday"
                intWeekday = "三"
                Exit Select
            Case "Thursday"
                intWeekday = "四"
                Exit Select
            Case "Friday"
                intWeekday = "五"
                Exit Select
            Case "Saturday"
                intWeekday = "六"
                Exit Select
            Case "Sunday"
                intWeekday = "日"
                Exit Select
            Case Else
                intWeekday = ""
                Exit Select
        End Select
        Return intWeekday
    End Function

    Public Shared Function EmailAddressCheck(ByVal emailAddress As String) As Boolean

        Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
        Dim emailAddressMatch As Match = Regex.Match(emailAddress, pattern)
        If emailAddressMatch.Success Then
            EmailAddressCheck = True
        Else
            EmailAddressCheck = False
        End If

    End Function

    Public Shared Sub SetDateLocale(ByVal region As String)
        Select Case region
            Case "zh-TW"
                Dim culture As New System.Globalization.CultureInfo("zh-TW")

                culture.DateTimeFormat.Calendar = New System.Globalization.TaiwanCalendar()
                culture.DateTimeFormat.ShortDatePattern = "yyyy.MM.dd"
                culture.DateTimeFormat.YearMonthPattern = "民國yy年MM月"
                culture.DateTimeFormat.ShortTimePattern = ""
                culture.DateTimeFormat.LongTimePattern = ""

                System.Threading.Thread.CurrentThread.CurrentCulture = culture
                System.Threading.Thread.CurrentThread.CurrentUICulture = culture
            Case ""
                Dim culture As New System.Globalization.CultureInfo("zh-TW", False)
                culture.DateTimeFormat.Calendar = New System.Globalization.GregorianCalendar()

                System.Threading.Thread.CurrentThread.CurrentCulture = culture
                System.Threading.Thread.CurrentThread.CurrentUICulture = culture
        End Select
    End Sub

    '取得限辦日期
    Public Shared Function GetLimitDate(ByVal TheDateTime As DateTime) As DateTime
        Dim AddCount As Integer = 1
        Dim LimitCount As Integer = 3 '限辦天數
        Dim LimitDate As DateTime = TheDateTime

        If TheDateTime.ToString("HH") >= 16 Then
            '超過下午四點,多加一天
            LimitCount += 1
        End If

        While AddCount <= LimitCount
            LimitDate = LimitDate.AddDays(1)
            If LimitDate.DayOfWeek <> DayOfWeek.Saturday And LimitDate.DayOfWeek <> DayOfWeek.Sunday Then
                AddCount += 1
            End If
        End While

        Return LimitDate
    End Function
End Class
